

/**
	AUTHOR		: VEERU
	DATE		: FEB 15th 2004
	DESCRIPTION	: THIS PROCEDURE IS USED TO GET THE SHIPPERS WHO ARE MAPPED TO PARTICULAR VENDOR 
*/

CREATE  PROC SP_GET_SHIPPERS_LIST

@VENDORID INT
AS
BEGIN

	DECLARE @RESULTS TABLE( SHIPPER_ID INT, SHIPPER_NAME VARCHAR(100), FLAG VARCHAR(10) )
	
	INSERT INTO @RESULTS( SHIPPER_ID, SHIPPER_NAME )  SELECT  SHIPPER_ID, SHIPPER_NAME FROM SHIPPER  WHERE DEACTIVATED <> 1 ORDER BY SHIPPER_NAME
	
	DECLARE SHIPPERLIST CURSOR FOR SELECT  VSR.SHIPPER_ID FROM VENDOR_SHIPPER_RELATIONSHIP VSR, CUSTOMER_RELATIONSHIP_STATUS CRS WHERE VENDOR_ID = @VENDORID AND VSR.RELATIONSHIPSTATUS_ID = CRS.RELATIONSHIPSTATUS_ID AND CRS.NAME = 'ACTIVE'
	DECLARE @SHIPPER_ID INT

	OPEN SHIPPERLIST
		FETCH NEXT FROM SHIPPERLIST INTO @SHIPPER_ID
		WHILE @@FETCH_STATUS = 0
		BEGIN
			UPDATE @RESULTS SET FLAG = 'CHECKED' WHERE SHIPPER_ID = @SHIPPER_ID
			FETCH NEXT FROM SHIPPERLIST INTO @SHIPPER_ID
		END
	CLOSE SHIPPERLIST
	DEALLOCATE SHIPPERLIST
	
	SELECT * FROM @RESULTS
END
